package org.example.managementsystem.repository;

import org.example.managementsystem.model.Material;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.List;
import java.util.Objects;

@Repository
public class MaterialDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public MaterialDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加物料
     */
    public Long insertMaterial(Material material) {
        String sql = "INSERT INTO material (material_number, material_name, material_type, " +
                "provider_name, factory_address, storage_date, specification, " +
                "contact_person, contact_phone, material_desc) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, material.getMaterialNumber());
            ps.setString(2, material.getName());
            ps.setString(3, material.getType());
            ps.setString(4, material.getProviderName());
            ps.setString(5, material.getProviderAddress());
            ps.setDate(6, material.getStorageDate() != null ?
                    Date.valueOf(material.getStorageDate()) : null);
            ps.setString(7, material.getSpecification());
            ps.setString(8, material.getContactPerson());
            ps.setString(9, material.getContactPhone());
            ps.setString(10, material.getDescription());
            return ps;
        }, keyHolder);

        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    /**
     * 更新物料信息
     */
    public int updateMaterial(Material material) {
        String sql = "UPDATE material SET material_number = ?, material_name = ?, material_type = ?, " +
                "provider_name = ?, factory_address = ?, storage_date = ?, " +
                "specification = ?, contact_person = ?, contact_phone = ?, material_desc = ? WHERE id = ?";

        return jdbcTemplate.update(sql,
                material.getMaterialNumber(),
                material.getName(),
                material.getType(),
                material.getProviderName(),
                material.getProviderAddress(),
                material.getStorageDate() != null ? Date.valueOf(material.getStorageDate()) : null,
                material.getSpecification(),
                material.getContactPerson(),
                material.getContactPhone(),
                material.getDescription(),
                material.getId());
    }

    /**
     * 删除物料
     */
    public int deleteMaterial(Long id) {
        String sql = "DELETE FROM material WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 根据ID查询物料
     */
    public Material getMaterialById(Long id) {
        String sql = "SELECT * FROM material WHERE id = ?";
        List<Material> materials = jdbcTemplate.query(sql, new MaterialRowMapper(), id);
        return materials.isEmpty() ? null : materials.get(0);
    }

    /**
     * 根据供货商查询物料
     */
    public List<Material> getMaterialsByProvider(String providerName) {
        String sql = "SELECT * FROM material WHERE provider_name = ?";
        return jdbcTemplate.query(sql, new MaterialRowMapper(), providerName);
    }

    /**
     * 查询所有物料
     */
    public List<Material> getAllMaterials() {
        String sql = "SELECT * FROM material";
        return jdbcTemplate.query(sql, new MaterialRowMapper());
    }

    /**
     * 搜索物料
     */
    public List<Material> searchMaterials(String keyword) {
        String sql = "SELECT * FROM material WHERE material_number LIKE ? " +
                "OR material_name LIKE ? OR material_type LIKE ? OR provider_name LIKE ? " +
                "OR contact_person LIKE ? OR contact_phone LIKE ?";

        String searchParam = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new MaterialRowMapper(),
                searchParam, searchParam, searchParam, searchParam, searchParam, searchParam);
    }

    /**
     * 批量更新空物料编号
     * 根据物料类型自动生成编号
     */
    public int batchUpdateEmptyMaterialNumbers() {
        String sql = "UPDATE material SET material_number = " +
                "CASE material_type " +
                "WHEN '金属' THEN CONCAT('JS', id) " +
                "WHEN '合金' THEN CONCAT('HJ', id) " +
                "WHEN '复合材料' THEN CONCAT('FH', id) " +
                "WHEN '其它' THEN CONCAT('QT', id) " +
                "ELSE CONCAT('QT', id) " +
                "END " +
                "WHERE material_number IS NULL OR material_number = ''";

        return jdbcTemplate.update(sql);
    }

    /**
     * 物料行映射器
     */
    private static class MaterialRowMapper implements RowMapper<Material> {
        @Override
        public Material mapRow(ResultSet rs, int rowNum) throws SQLException {
            Material material = new Material();
            material.setId(rs.getLong("id"));
            material.setMaterialNumber(rs.getString("material_number"));
            material.setName(rs.getString("material_name"));
            material.setType(rs.getString("material_type"));
            material.setProviderName(rs.getString("provider_name"));
            material.setProviderAddress(rs.getString("factory_address"));

            Date storageDate = rs.getDate("storage_date");
            if (storageDate != null) {
                material.setStorageDate(storageDate.toLocalDate());
                // 为了兼容前端，设置entryDate字段
                material.setEntryDate(storageDate.toLocalDate().toString());
            }

            material.setSpecification(rs.getString("specification"));
            material.setContactPerson(rs.getString("contact_person"));
            material.setContactPhone(rs.getString("contact_phone"));
            material.setDescription(rs.getString("material_desc"));

            // 为了兼容前端，设置contact和phone字段
            material.setContact(rs.getString("contact_person"));
            material.setPhone(rs.getString("contact_phone"));

            return material;
        }
    }
}
